A look at the latest Jedlovec House eletricity usage and solar production from PPL and Enphase.
Load packages
library(tidyverse)
library(lubridate)
library(hms)
library(readxl)
Load PPL data
col_datatypes <- c('numeric','numeric','date','text',rep('numeric',99))
#?read_excel
hourly1 <- read_excel("Hourly Usage 220326 to 220624.xlsx", col_types = col_datatypes)
Warning: Expecting numeric in A277 / R277C1: got 'The information contained in this file is intended for the confidential use by the customer and third parties authorized by the customer to receive the information. Any unauthorized use is prohibited.'
hourly2 <- read_excel("Hourly Usage 220625 to 230623.xlsx", col_types = col_datatypes)
Warning: Expecting numeric in A1096 / R1096C1: got 'The information contained in this file is intended for the confidential use by the customer and third parties authorized by the customer to receive the information. Any unauthorized use is prohibited.'
hourly3 <- read_excel("PPL 230624 to 240509.xlsx", col_types = col_datatypes)
Warning: Expecting numeric in A967 / R967C1: got 'The information contained in this file is intended for the confidential use by the customer and third parties authorized by the customer to receive the information. Any unauthorized use is prohibited.'
hourly1
hourly2
hourly3
ppl_15mins <- bind_rows(hourly1,list(hourly2,hourly3))
ppl_15mins %>% arrange(desc(Date), `Read Type`)
NA
NA
Transform PPL Data
hourly_ppl_pivot <- ppl_15mins %>%
rename(date = Date) %>%
pivot_longer(!c("Account Number", "Meter Number", date, "Read Type", Min, Max, Total), names_to = "time", values_to = "kWh")
rename(ppl_15mins, date = Date)
hourly_ppl_pivot <- hourly_ppl_pivot %>%
mutate(time = parse_time(time, '%H:%M %p'), month = month(date, label=TRUE), year = year(date), yday = yday(date), wday = wday(date, label=TRUE))
(hourly_ppl_net <- hourly_ppl_pivot %>%
filter(`Read Type` == "kWh Net"))
Load Enphase data
import <- read_csv("enphase_history_230701.csv")
Rows: 42432 Columns: 2── Column specification ────────────────────────────────────────────────────────────────────────────────────────
Delimiter: ","
chr (1): Date/Time
dbl (1): Energy Produced (Wh)
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
update <- read_csv("hourly_generation_230625_240510.csv")
Rows: 30816 Columns: 2── Column specification ────────────────────────────────────────────────────────────────────────────────────────
Delimiter: ","
chr (1): Date/Time
dbl (1): Energy Produced (Wh)
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
update <- update %>%
mutate(`Date/Time` = as.POSIXct(`Date/Time`,format="%m/%d/%Y %H:%M",tz=Sys.timezone())) %>%
filter(`Date/Time` >= as.POSIXct('07/01/2023 00:00',format="%m/%d/%Y %H:%M",tz=Sys.timezone()) )
import <- import %>%
mutate(`Date/Time` = as.POSIXct(`Date/Time`,format="%m/%d/%Y %H:%M",tz=Sys.timezone()))
full_hist <- rbind(import,update)
full_hist %>% arrange(desc(`Date/Time`))
(hourly_production <- full_hist %>%
rename(datetime = `Date/Time`, energy_produced_Wh = `Energy Produced (Wh)`) %>%
mutate(datetime = as.POSIXct(datetime,format="%m/%d/%Y %H:%M",tz=Sys.timezone())) %>%
mutate(date = date(datetime), time = as.hms(format(datetime, format = "%H:%M:%S")), month = month(datetime, label=TRUE), year = year(datetime), day = day(datetime), yday = yday(datetime), monthday = format(datetime, "%m-%d"), wday = wday(datetime, label=TRUE), equinox_day = (yday + 10) %% 365, equinox_group = floor((equinox_day+15)/30)*30)
)
NA
Spot-check Enphase Should see lifetime production by day and by hour
ggplot(hourly_production, aes(datetime, energy_produced_Wh)) +
geom_point()
ggplot(hourly_production, aes(time, energy_produced_Wh)) +
theme(axis.text.x = element_text(angle = 90)) +
geom_point()
Net + Produced = Consumed
# hourly_ppl_net <- hourly_ppl_net %>% mutate(date = as_date(date))
#hourly_ppl_net %>% arrange(desc(date))
#hourly_production %>% arrange(desc(date))
(hourly_electricity <- hourly_ppl_net %>%
inner_join(hourly_production, by = join_by(date,time)) %>%
mutate(consumed_kWh = kWh + energy_produced_Wh/1000, produced_kWh = energy_produced_Wh/1000) %>%
rename(net_kWh = kWh) %>%
select(datetime, date, time, net_kWh, produced_kWh, consumed_kWh) %>%
arrange(date))
NA
daily_electricity <- hourly_electricity %>%
group_by(date) %>%
summarize(net_kWh = sum(net_kWh), produced_kWh = sum(produced_kWh), consumed_kWh = sum(consumed_kWh))
daily_electricity %>%
filter(date >= as.POSIXct('04/16/2022 00:00',format="%m/%d/%Y %H:%M",tz=Sys.timezone()) & date < as.POSIXct('04/16/2023 00:00',format="%m/%d/%Y %H:%M",tz=Sys.timezone()) ) %>%
summarize(net_kWh = sum(net_kWh), produced_kWh = sum(produced_kWh), consumed_kWh = sum(consumed_kWh))
NA
hourly_electricity %>%
mutate(solar_year = case_when(
datetime < as.POSIXct('04/16/2022 00:00',format="%m/%d/%Y %H:%M",tz=Sys.timezone()) ~ 0,
datetime >= as.POSIXct('04/16/2022 00:00',format="%m/%d/%Y %H:%M",tz=Sys.timezone()) &
datetime < as.POSIXct('04/16/2023 00:00',format="%m/%d/%Y %H:%M",tz=Sys.timezone()) ~ 1,
datetime >= as.POSIXct('04/16/2023 00:00',format="%m/%d/%Y %H:%M",tz=Sys.timezone()) &
datetime < as.POSIXct('04/16/2024 00:00',format="%m/%d/%Y %H:%M",tz=Sys.timezone()) ~ 2,
TRUE ~ 3)
) %>%
group_by(solar_year) %>%
summarize(net_kWh = sum(net_kWh), produced_kWh = sum(produced_kWh), consumed_kWh = sum(consumed_kWh))
NA
NA
hourly_electricity %>% summarize(min_date = min(date), max_date = max(date))
electricity_by_time <- hourly_electricity %>%
#filter(date <= as.POSIXct('12/31/2022 00:00',format="%m/%d/%Y %H:%M",tz=Sys.timezone()) ) %>%
group_by(time) %>%
summarize(produced_kWh = mean(produced_kWh), consumed_kWh = mean(consumed_kWh), net_kWh = mean(net_kWh)) %>%
arrange(time)
electricity_by_time
ggplot(electricity_by_time, aes(x=time)) +
geom_point(aes(y=consumed_kWh, color="Consumed")) +
geom_point(aes(y=net_kWh, color="Net")) +
geom_point(aes(y=produced_kWh*-1, color="Produced")) +
labs(colour="",x="Time of Day",y="Electricity Consumption (kWh)")+
scale_color_manual(values = c("red","black","green")) +
ggtitle("Home Electricity in 15-Minute Intervals (Since April 15, 2022)")
electricity_by_month_time <- hourly_electricity %>%
mutate(month = as_factor(month(date))) %>%
filter(year(date) == 2023) %>%
group_by(month, time) %>%
summarize(produced_kWh = mean(produced_kWh), consumed_kWh = mean(consumed_kWh), net_kWh = mean(net_kWh)) %>%
arrange(month, time)
`summarise()` has grouped output by 'month'. You can override using the `.groups` argument.
ggplot(electricity_by_month_time, aes(time, consumed_kWh, group=month, color=month)) +
geom_point() +
ggtitle("Home Electricity in 15-Minute Intervals") +
ylab("Net Electricity Consumption (kWh)")
NA
NA